package com.fsbm.ctech.maxis.ebpsbackend;

import com.checkfree.isolutions.customer.persist.*;
import com.checkfree.isolutions.persist.*;
import com.checkfree.isolutions.statement.*;
import com.checkfree.isolutions.statement.persist.*;
import com.checkfree.isolutions.utility.*;
import com.checkfree.isolutions.admin.*;
import com.checkfree.isolutions.streaming.*;
import com.checkfree.isolutions.admin.ObjectId;

import java.io.File;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Date;
import java.util.Vector;
import java.util.Properties;


import java.io.*;
import java.text.*;
import java.util.*;
import java.util.Date;
import java.sql.*;

/**
 * Batch Program to write Notification required files. Reads objects from the CheckFree DB thru
 * API to build the CSV files. It will stream the CSV and will build the zip files for transfer.
 * Instantiates an FTP and telnet session with the SMTP Server to transfer files.
 * <p>
 * Maxis Backend Program - Deliver Bills in customers inbox for subscriptions or adhoc requests.
 *
 * @author Michael Ty Chan
 */

public class RemoveStatement {
    static ConfigObject cfgObj = new ConfigObject();
    static int intFileSize = cfgObj.maxFileAmount;
    static String[][] strSubsArrayFileName = new String[intFileSize][intFileSize];
    static String[][] strAdhocArrayFileName = new String[intFileSize][intFileSize];
	static int maxThread = 1;

    public static void main(String argv[]) {
        String strFile = "",strStmtDate="";
        for (int optind = 0; optind < argv.length; optind++) {
            if (argv[optind].toLowerCase().indexOf("-file") != -1) {
                strFile = argv[optind];
                strFile = strFile.substring(strFile.indexOf("=")+1);
            } else if (argv[optind].toLowerCase().indexOf("-date") != -1) {
                strStmtDate = argv[optind];
                strStmtDate = strStmtDate.substring(strStmtDate.indexOf("=")+1);
            } else if (argv[optind].toLowerCase().startsWith("-h")) {
                printUsage();
                System.exit(1);
            } else {
                break;
            }
        }
        // Main Program Call
        startMain(strFile,strStmtDate);
        System.exit(0);
    }

    // Start of sub-routines
    public static void startMain(String strFile,String strStmtDate) {

        try{
			ArrayList arLStmtIds = extractStatementIds(strFile);

			if(removeStatements(arLStmtIds,strStmtDate)){
        	    cfgObj.write("Successfully deleted all statements from statement table!!", "RemoveStatement");
			}


		}catch (Exception ex) {
        	    cfgObj.write("Process Name(runner)- " + ex.getClass() + " " + ex.getMessage(), "RemoveStatement");
       	}
    }


    	// Extract the statementrun IDs from the billProcFile
    	public static ArrayList extractStatementIds(String fileName){
    	    String lineFileReader    = "";
    	    ArrayList runIds         = new ArrayList();
    	    try {
    	        RandomAccessFile raf = new RandomAccessFile(fileName, "rw");
    	        boolean lnvalue      = true;
    	        String line          = "";
    	        int iCount           = 0;
    	        String[] strAccounts = new String[1024];
    	        while (lnvalue){
    	            line = raf.readLine();
    	            if (line == null){                                         //when end of file
    	                lnvalue      = false; }
    	            else{
    	                runIds.add(line);
    	            }
    	        }
    	        raf.close();
    	    }
    	    catch (Exception ex) {
    	        cfgObj.write("Process Name(extractStatementIds)- " + ex.getClass() + " " + ex.getMessage(),"RemoveStatement");
    	    }
    	    return runIds;
    	}

    	// Extract the statementrun IDs from the billProcFile
    	public static boolean removeStatements(ArrayList stmtIds,String stmtDate){
    	    boolean boolResult = true;
		    Connection connection = null;
		    PreparedStatement pstmt = null;
		    PreparedStatement pstmt1 = null;
	        String query = "";
	        String query1 = "";
	        int intResult=0;
	        int intResult1=0;

	        try{
				Class.forName(cfgObj.databaseDriver);
				connection = DriverManager.getConnection(cfgObj.databaseSourceName, cfgObj.databaseUserID, cfgObj.databasePassword);
	            connection.setAutoCommit(false);

				if (connection!=null){
					System.out.println("Before the for loop.....");
					for(int i=0;i<stmtIds.size();i++){
						String stmtId = (String)stmtIds.get(i);
						query = "DELETE FROM EXTERNALFORMAT WHERE STATEMENTID IN (SELECT S.ID FROM STATEMENT S, ACCOUNT A WHERE A.ID = S.ACCOUNTID AND A.EXTERNALACCOUNTKEY = '"+stmtId+"' AND TO_DATE(STATEMENTDATE,'J')='"+stmtDate+"')";
						query1 = "DELETE FROM STATEMENT WHERE ID IN(SELECT S.ID FROM STATEMENT S, ACCOUNT A WHERE S.ACCOUNTID = A.ID AND A.EXTERNALACCOUNTKEY = '"+stmtId+"' AND TO_DATE(STATEMENTDATE,'J') = '"+stmtDate+"')";

						// prepare the statement and bind parameter
	    		        pstmt = connection.prepareStatement(query);
	    		        pstmt1 = connection.prepareStatement(query1);
	    		        intResult=pstmt.executeUpdate();
	    		        intResult1=pstmt1.executeUpdate();

    		          	if(intResult >0 || intResult1>0){
							boolResult = true;
			    	        cfgObj.write("Succesfully deleted account number "+stmtId+" with statement date "+stmtDate,"RemoveStatement");
						}else{
							boolResult = true;
			    	        cfgObj.write("Failed to delete account number "+stmtId+" with statement date "+stmtDate,"RemoveStatement");
						}

			        }// end for
			        //pstmt.close();
			        pstmt1.close();
				}
            connection.commit();
			cfgObj.write("Committing  transaction....","RemoveStatement");
            connection.setAutoCommit(true);
			connection.close();
    	    }
    	    catch (Exception ex) {
				boolResult = false;
    	        cfgObj.write("Process Name(removeStatements)- " + ex.getClass() + " " + ex.getMessage(),"RemoveStatement");
    	    }
    	    return boolResult;
    	}


    	public static void printUsage() {
    	      System.out.println("\nCopyright (c) 2002 FSBM CTech Sdn Bhd (427095-M).");
    	      System.out.println("All rights reserved.");
    	      System.out.println("\n\nUsage : deliver.sh [-DeliveryOptions=0|1|2] [-ProcFile=FILENAME]");
    	      System.out.println("or :");
    	      System.out.println("\tjava -server -Xms[HS] -Xmx[HS] -DBGROOT=/app/cfi Deliver");
    	      System.out.println("\t[-DeliveryOptions=0|1|2] [-ProcFile=FILENAME]");
    	      System.out.println("\n\nWhere:");
    	      System.out.println("\t -[FILENAME] where file.txt is the output of billproc");
    	      System.out.println("\t -[HS] Min and Max Heap Memory size allocation");
    	      System.out.println("\t -[0] Subscription and Adhoc requests(procFile param req'd)");
    	      System.out.println("\t -[1] Subscription only(procFile option req'd)");
    	      System.out.println("\t -[2] Adhoc request only");
    	      System.out.println("\n\tor java Deliver -h for help options");
    	      System.out.println("\tor deliver.sh -h for help options");
    	 }

	} // end class Deliver

